package com.jet.chat.service;


import java.io.Reader;
import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

import com.jet.chat.exception.ChatExceptionCheckAuthSQLException;
import com.jet.chat.exception.ChatExceptionDBService;

//import com.jet.multichat.server.ChatServer;

public class DBService {
	private String dbURL;
	//= "jdbc:derby:mydb;create=true";
	//= "jdbc:derby://localhost:1527/mydb";
	//;create=true;user=me;password=mine
	private Connection conn = null;
	private Statement stmt = null;
	
	public DBService(String dbname) {
		dbURL = "jdbc:derby:" + dbname + ";create=true";
	}

	public boolean isConnected() {
		return conn != null;
	}

	public void createConnection() throws ChatExceptionDBService {
			//Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
			try {
				Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			} catch (InstantiationException e) {
				throw new ChatExceptionDBService("ошибка инициализирования пользователя базы данных");
				//e.printStackTrace();
			} catch (IllegalAccessException e) {
				throw new ChatExceptionDBService("ошибка доступа базы данных");
				//e.printStackTrace();
			} catch (ClassNotFoundException e) {
				throw new ChatExceptionDBService("драйвер базы данных не найден");
				//e.printStackTrace();
			}
			
			try {
				conn = DriverManager.getConnection(dbURL);
			} catch (SQLException e) {
				throw new ChatExceptionDBService("ошибка адреса базы данных");
				//e.printStackTrace();
			}
	}
	
	public void shutdown() {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				//DriverManager.getConnection(dbURL + ";shutdown=true");
				conn.close();
			}           
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void execUPD(String sql) {
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void execSQL(String sql) {
		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = results.getMetaData();
			int numberCols = rsmd.getColumnCount();

			String resultString = "";

			for (int i=1; i<=numberCols; i++) {
				resultString = resultString + rsmd.getColumnLabel(i) + "\t\t";
			}

			System.out.println(resultString);
			resultString = "";

			while(results.next()) {
				for (int i=1; i<=numberCols; i++) {
					resultString = resultString + results.getString(i) + "\t\t";
				}
				
				System.out.println(resultString);
				resultString = "";
			}
			results.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public synchronized void addHistory(int senderId, int recieverId, String channelName, String content) {
		try {
			Reader reader = new StringReader(content);
			if (content == null || content.equals(null)) {
				
			} else {
				if (reader == null || reader.equals(null)) {
					
				} else {
					stmt = conn.createStatement();
					ResultSet results = stmt.executeQuery("select max(id) from history");
					int id = 0;
					while(results.next()) {
						id = results.getInt(1);
					}
					id = id + 1;
					results.close();
					stmt.close();
					
					PreparedStatement ps = conn.prepareStatement("insert into history (id, senderid, receiverid, channelname, content) values (?, ?, ?, ?, ?)");
					ps.setInt(1, id);
					ps.setInt(2, senderId);
					ps.setInt(3, recieverId);
					ps.setString(4, channelName);
					ps.setClob(5, reader);
					ps.execute();
					conn.commit();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public synchronized ArrayList<Properties> getChannelHistory(String ChannelName) {
		ArrayList<Properties> ret = new ArrayList<Properties>();
		try {
			PreparedStatement ps;
			ps = conn.prepareStatement("select u.username, h.content from history as h, users as u where h.channelname = ? and u.id = h.senderid and h.receiverid = ?");
			ps.setString(1, ChannelName);
			ps.setInt(2, 0);
			ResultSet results = ps.executeQuery();
			while(results.next()) {
				Properties raw = new Properties();
				raw.put("username", results.getString(1));
				raw.put("content", results.getString(2));
				ret.add(raw);
			}
			results.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	public synchronized ArrayList<Properties> getWhisperHistory(int userId) {
		ArrayList<Properties> ret = new ArrayList<Properties>();
		try {
			PreparedStatement ps;
			ps = conn.prepareStatement("select u.username, h.content from history as h, users as u where h.receiverid = ? and u.id = h.senderid");
			ps.setInt(1, userId);
			ResultSet results = ps.executeQuery();
			while(results.next()) {
				Properties raw = new Properties();
				raw.put("username", results.getString(1));
				raw.put("content", results.getString(2));
				ret.add(raw);
			}
			results.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	public synchronized ArrayList<Properties> getHistory(int userId) {
		ArrayList<Properties> ret = new ArrayList<Properties>();
		try {
			PreparedStatement ps;
			ps = conn.prepareStatement("select u.username, h.receiverid, h.channelname, h.content from history as h, users as u where (h.receiverid = ? or h.receiverid = ?) and u.id = h.senderid and h.channelname in (select channelname from history where senderid = ? group by channelname)");
			ps.setInt(1, userId);
			ps.setInt(2, 0);
			ps.setInt(3, userId);
			ResultSet results = ps.executeQuery();
			while(results.next()) {
				Properties raw = new Properties();
				raw.put("username", results.getString(1));
				raw.put("receiverid", results.getInt(2));
				raw.put("channelname", results.getString(3));
				raw.put("content", results.getString(4));
				ret.add(raw);
			}
			results.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	public int checkAuth(String username, String password) throws ChatExceptionCheckAuthSQLException {
		int userId = 0;
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("select id from users where username = ? and password = ?");
			ps.setString(1, username);
			ps.setString(2, password);
			ResultSet results = ps.executeQuery();
			while(results.next()) {
				userId = results.getInt(1);
			}
			results.close();
		} catch (SQLException e) {
			throw new ChatExceptionCheckAuthSQLException("ошибка при авторизации");
			//e.printStackTrace();
		}
		return userId;
	}

	public static void main(String[] args) {
		//ChatDBCreate chatDBCreate = new ChatDBCreate("cdb01");
		//DBService dbService = new DBService("mydb");
		
		//dbService.createConnection();
		
//		dbService.execUPD("create table users (id int primary key not null, username varchar(20), password varchar(20))");
//		dbService.execUPD("insert into users (id, username, password) values (1, 'alisa', 'tao')");
//		dbService.execUPD("insert into users (id, username, password) values (2, '1', '1')");
//		dbService.execUPD("insert into users (id, username, password) values (3, '2', '2')");
//		dbService.execSQL("select * from users");
//		dbService.execUPD("drop table users");
		
//		System.out.println(new Timestamp(new Date().getTime()));
//		dbService.execUPD("create table history (date timestamp, content varchar(100))");
//		dbService.execUPD("insert into history (date, content) values ('" + new Timestamp(new Date().getTime()) + "', 'asd')");
//		dbService.execSQL("select * from history");
//		dbService.execUPD("drop table history");
		
		//dbService.shutdown();
	}

}
